import mysql from "mysql2";
import {config} from "dotenv";

config();

const pool = mysql
  .createPool({
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
  })
  .promise();

const tableName = "cats";

const createDataBase = () => {
  pool.query(`CREATE DATABASE cats_db`)
}

const createTable = () => {
  pool.query(`
        CREATE TABLE ${tableName} (
        id integer PRIMARY KEY AUTO_INCREMENT,
        breed VARCHAR (255) NOT NULL,
        imageurl VARCHAR (255) NOT NULL,
        age INT NOT NULL
    );`);
};

const dropTable = () => {
  pool.query(`DROP TABLE ${tableName}`);
};


const checkTable = async () => {
  return (
    await pool.query(`
    SELECT count(*)
    FROM information_schema.TABLES
    WHERE (TABLE_NAME = '${tableName}')
    `)
  )[0][0]["count(*)"];
};


const clearTable = () => {
  pool.query(`DELETE FROM ${tableName}`);
};

//#region export function

export const getAll = () => {
  return pool.query(`SELECT * FROM ${tableName};`);
};

export const getSortData = (breed?: string, age?: number, size?: number) => {
  let sqlQuery = `SELECT * FROM ${tableName}`;
  
  const conditions : string[] = [];
  const values: (number | string)[] = [];

  if (breed) {
    conditions.push(`breed = ?`);
    values.push(breed); 
  }
  if (age) {
    conditions.push(`age = ?`);
    values.push(age)
  }
  if (size) values.push(size);

  if (conditions.length > 0) sqlQuery += ' WHERE ' + conditions.join(' AND ');

  sqlQuery += ` LIMIT ?`;

  return pool.query(sqlQuery, values);
};

export const getSortByAge = (sort:number) => {
  return pool.query(`SELECT * FROM ${tableName} ORDER BY ABS(age) ${sort === 0 ? 'DESC' : ''}`);
}

export const addToBase = (breed: string, imageurl: string, age: number) => {
  return pool.query(
    `INSERT INTO ${tableName} (breed,imageurl,age) VALUE (?,?,?)`,
    [breed, imageurl, age]
  );
};

export const deleteById = (id: number) => {
  return pool.query(`DELETE FROM ${tableName} WHERE id = ?`, [id]);
};


export const tableExists = () => {
  checkTable().then((data) => (data === 0 ? createTable() : clearTable()));
};

//#endregion
